using Npgsql;
using System;
using System.Threading.Tasks;

namespace AddRoleStatusColumn
{
    class Program
    {
        static async Task Main(string[] args)
        {
            // 数据库连接字符串
            string connectionString = "Host=localhost;Database=GroupSixDb;Username=postgres;Password=123456";

            try
            {
                using (var connection = new NpgsqlConnection(connectionString))
                {
                    await connection.OpenAsync();
                    Console.WriteLine("数据库连接成功");

                    // 检查Status字段是否已存在
                    using (var command = new NpgsqlCommand(
                        "SELECT column_name FROM information_schema.columns WHERE table_name = 'gs_roles' AND column_name = 'Status'", 
                        connection))
                    {
                        var result = await command.ExecuteScalarAsync();
                        if (result != null)
                        {
                            Console.WriteLine("Status字段已存在，无需添加");
                            return;
                        }
                    }

                    // 添加Status字段
                    using (var command = new NpgsqlCommand(
                        "ALTER TABLE gs_roles ADD COLUMN \"Status\" character varying(20) NOT NULL DEFAULT 'active'", 
                        connection))
                    {
                        await command.ExecuteNonQueryAsync();
                        Console.WriteLine("成功添加Status字段到gs_roles表");
                    }

                    // 更新现有记录的Status字段
                    using (var command = new NpgsqlCommand(
                        "UPDATE gs_roles SET \"Status\" = 'active' WHERE \"Status\" IS NULL", 
                        connection))
                    {
                        var affectedRows = await command.ExecuteNonQueryAsync();
                        Console.WriteLine($"更新了 {affectedRows} 条记录的Status字段");
                    }

                    // 验证字段是否添加成功
                    using (var command = new NpgsqlCommand(
                        "SELECT column_name, data_type, is_nullable, column_default FROM information_schema.columns WHERE table_name = 'gs_roles' AND column_name = 'Status'", 
                        connection))
                    {
                        using (var reader = await command.ExecuteReaderAsync())
                        {
                            if (await reader.ReadAsync())
                            {
                                Console.WriteLine($"字段验证成功:");
                                Console.WriteLine($"  字段名: {reader["column_name"]}");
                                Console.WriteLine($"  数据类型: {reader["data_type"]}");
                                Console.WriteLine($"  是否可空: {reader["is_nullable"]}");
                                Console.WriteLine($"  默认值: {reader["column_default"]}");
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"错误: {ex.Message}");
                Console.WriteLine($"详细错误: {ex}");
            }

            Console.WriteLine("按任意键退出...");
            Console.ReadKey();
        }
    }
} 